﻿using System;
using System.Collections.Generic;
using System.Text;
using combit.ListLabel17.DataProviders;
using System.Data;
using System.Runtime.Serialization;
using System.Security.Permissions;
using System.Globalization;
using IBM.Data.DB2;
using System.Text.RegularExpressions;
using System.Collections.ObjectModel;

namespace combit.ListLabel17.DataProviders
{
    /// <summary>
    /// Provider for DB2, see http://www-01.ibm.com/software/data/db2/ad/dotnet.html
    /// Tested with version 9.0.0.2
    /// </summary>
    [Serializable]
    public sealed class DB2ConnectionDataProvider : DbConnectionDataProvider, ISerializable
    {
        private ReadOnlyCollection<String> _tableSchemas;

        private DB2ConnectionDataProvider()
            : base()
        {
        }

        private DB2ConnectionDataProvider(SerializationInfo info, StreamingContext context)
            : base(info, context)
        {
            if (info.GetInt32("DB2ConnectionDataProvider.Version") >= 1)
            {
                Connection = new DB2Connection();
                Connection.ConnectionString = info.GetString("ConnectionString");
                SupportedElementTypes = (DbConnectionElementTypes)info.GetInt32("SupportedElementTypes");
                Provider.CancelBeforeClose = false;
                PrefixTableNameWithSchema = info.GetBoolean("PrefixTableNameWithSchema");
                _tableSchemas = (ReadOnlyCollection<string>)info.GetValue("TableSchemas", typeof(ReadOnlyCollection<string>));
            }
        }

        public DB2ConnectionDataProvider(DB2Connection connection)
        {
            Connection = connection as IDbConnection;
            SupportedElementTypes = DbConnectionElementTypes.Table;
            Provider.CancelBeforeClose = false;
        }

        public DB2ConnectionDataProvider(DB2Connection connection, ReadOnlyCollection<string> tableSchemas)
        {
            Connection = ((connection as ICloneable).Clone()) as DB2Connection;
            SupportedElementTypes = DbConnectionElementTypes.Table;
            _tableSchemas = tableSchemas;
        }

        public DB2ConnectionDataProvider(DB2Connection connection, string tableSchema)
        {
            Connection = ((connection as ICloneable).Clone()) as DB2Connection;
            SupportedElementTypes = DbConnectionElementTypes.Table;
            List<string> schemas = new List<string>();
            if (!String.IsNullOrEmpty(tableSchema))
                schemas.Add(tableSchema);
            _tableSchemas = schemas.AsReadOnly();
        }

        public DbConnectionElementTypes SupportedElementTypes
        {
            get;
            set;
        }

        public bool PrefixTableNameWithSchema
        {
            get;
            set;
        }

        protected override void Init()
        {
            if (Initialized)
                return;

            List<String> passedRelationNames = new List<string>();          
            PrefixTableNameWithSchema = true;
            Connection.Open();
            DB2Command cmd;
            try
            {
                DataTable dt = (Connection as DB2Connection).GetSchema("Tables");
                Connection.Close();
                foreach (DataRow dr in dt.Rows)
                {
                    string tableSchema = dr["TABLE_SCHEMA"].ToString();
                    string tableType = dr["TABLE_TYPE"].ToString();
                    string parentTableName = dr["TABLE_NAME"].ToString();

                    if (tableSchema.Contains("SYSTOOLS"))
                        continue;

                    switch (tableType)
                    {

                        case "TABLE":                           
                            if ((SupportedElementTypes & DbConnectionElementTypes.Table) == 0)
                                continue;
                            break;

                        case "VIEW":
                            if ((SupportedElementTypes & DbConnectionElementTypes.View) == 0)
                                continue;
                            break;

                        default:
                            continue;
                    }
                    ICloneable cloneable = (ICloneable)Connection;
                    DB2Connection newConnection = (DB2Connection)cloneable.Clone();
                    cmd = new DB2Command("Select * From " + (String.IsNullOrEmpty(tableSchema) ? parentTableName + "" : tableSchema + "." + parentTableName) + "", newConnection);
                    Provider.AddCommand(cmd as IDbCommand, PrefixTableNameWithSchema ? String.Concat(tableSchema, ".", parentTableName) : parentTableName, "{0}", "?");
                }

                string commandText = "SELECT K.tabname, K.fk_colnames, K.reftabname, K.pk_colnames, K.tabschema, K.reftabschema FROM syscat.\"REFERENCES\" K";                                    
                cmd = new DB2Command(commandText, Connection as DB2Connection);
                Connection.Open();
                DB2DataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                
                while (reader.Read())
                {
                    if (!reader.IsDBNull(2) && !reader.IsDBNull(3))
                    {
                        string childColumnName = SplitString(reader.GetString(1).ToString());
                        string parentColumnName = SplitString(reader.GetString(3).ToString());
                        string childSchema = reader.GetString(4).ToString();
                        string parentSchema = reader.GetString(5).ToString();
                        string childTableName = SplitString(reader.GetString(0).ToString());
                        string parentTableName = SplitString(reader.GetString(2).ToString());
                        string relName = parentTableName + "2" + childTableName;
                        int relationIndex = 1;
                        string formatString = relName + "{0}";

                        while (passedRelationNames.Contains(relName))
                        {
                            relName = String.Format(CultureInfo.InvariantCulture, formatString, relationIndex);
                            relationIndex++;
                        }
                        passedRelationNames.Add(relName);
                        Provider.AddRelation(relName, PrefixTableNameWithSchema ? String.Concat(parentSchema, ".", parentTableName) : parentTableName, PrefixTableNameWithSchema ? String.Concat(childSchema, ".", childTableName) : childTableName, parentColumnName, childColumnName);                        
                    }
                }
                reader.Close();
            }
            finally
            {
                Connection.Close();
                Initialized = true;
            }
        }

        public string SplitString(string input)
        {

            string result = "";
            Regex r = new Regex("([^ ]+[ ]?[^ ]+)");
            MatchCollection mc = r.Matches(input);
            
            foreach (Match current in mc)
            {
                if (mc.Count > 1)
                    result += current.Groups[0].ToString() + "\t";
                else
                    result = current.Groups[0].ToString();
            }                     
            return result.TrimEnd('\t');            
        }
        
        #region ISerializable Members
        [SecurityPermissionAttribute(SecurityAction.Demand, SerializationFormatter = true)]
        public override void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            base.GetObjectData(info, context);
            info.AddValue("DB2ConnectionDataProvider.Version", 1);
            info.AddValue("ConnectionString", Connection.ConnectionString);
            info.AddValue("PrefixTableNameWithSchema", PrefixTableNameWithSchema);
            info.AddValue("TableSchemas", _tableSchemas);
        }
        #endregion
    }
}